package com.kaishengit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.kaishengit.entity.Idea;
import com.kaishengit.entity.Project;
import com.kaishengit.entity.User;
import com.kaishengit.util.DBHelp;
import com.kaishengit.util.RowMapper;

public class IdeaDao {
	private DBHelp<Idea> db=new DBHelp<Idea>();
	private CommentDao dao=new CommentDao();
	private MessageDao mdao=new MessageDao();
	
	public long findAount(String id){
		String sql = "select count(*) from t_idea where t_project_id =?";
		return (Long) db.executeForFunction(sql,id);
	}
	
	public List<Idea> findAllByProjectId(int offest, int pageSize, String projectid) {
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT ti.id,title,content,t_project_id,t_user_id,ti.createtime,tu.username,tu.password,tu.imgname,tp.name,tp.description,tp.createtime as time FROM t_idea AS ti ");
		sql.append("LEFT JOIN t_user AS tu ON tu.id=ti.t_user_id ");
		sql.append("LEFT JOIN  t_project AS tp ON tp.id = ti.t_project_id ");
		sql.append("WHERE ti.t_project_id = ? ORDER BY ti.createtime DESC  LIMIT ?,?");
		return db.executeQueryForList(sql.toString(), new IdeaRowMapper(),projectid,offest,pageSize);
	}
	public boolean save(Idea i) {
		String sql="INSERT INTO t_idea(id,title,content,t_project_id,t_user_id,createtime)values(?,?,?,?,?,?)";
		return db.executeSQL(sql, i.getId(),i.getTitle(),i.getContent(),i.getT_project_id(),i.getT_user_id(),i.getCreatetime());
	}

	
	public class IdeaRowMapper implements RowMapper<Idea>{

		public Idea mapRow(ResultSet rs) throws SQLException {
			Idea i=new Idea();
			
			i.setId(rs.getString("id"));
			i.setTitle(rs.getString("title"));
			i.setContent(rs.getString("content"));
			i.setT_project_id(rs.getString("t_project_id"));
			i.setT_user_id(rs.getString("t_user_id"));
			i.setCreatetime(rs.getString("createtime"));
			
			User user = new User();
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			user.setImgname(rs.getString("imgname"));
			user.setId(rs.getString("t_user_id"));
			i.setUser(user);
			
			Project p = new Project();
			p.setCreatetime(rs.getString("time"));
			p.setDescription(rs.getString("description"));
			p.setId(rs.getString("t_project_id"));
			p.setName(rs.getString("name"));
			i.setProject(p);
			
			return i;
		}
		
	}


	public Idea findByIdeaId(String ideaid) {
		String sql="SELECT id,title,content,t_project_id,t_user_id,createtime FROM t_idea WHERE id=?";
		return db.executeQueryForObject(sql, new IRowMapper(), ideaid);
	}

	public class IRowMapper implements RowMapper<Idea>{

		public Idea mapRow(ResultSet rs) throws SQLException {
			Idea idea=new Idea();
			idea.setId(rs.getString("id"));
			idea.setTitle(rs.getString("title"));
			idea.setContent(rs.getString("content"));
			idea.setT_project_id(rs.getString("t_project_id"));
			idea.setT_user_id(rs.getString("t_user_id"));
			idea.setCreatetime(rs.getString("createtime"));
			return idea;
		}
		
	}


	public boolean update(String ideaid,String title, String createtime, String desc,
			String projectid, String userid) {
		String sql="UPDATE t_idea SET id=?,title=?,content=?,t_project_id=?,t_user_id=?,createtime=? WHERE id=?";
		return db.executeSQL(sql, ideaid,title,desc,projectid,userid,createtime,ideaid);
		
	}

	public boolean del(String id){
		mdao.del(id);
		dao.del(id);
		String sql="delete from t_idea where id=?";
		return db.executeSQL(sql, id);
	}
}
